from flask import Flask, abort, request, jsonify
import mysql.connector

config = {
    "host": "127.0.0.1",
    "port": 3306,
    "database": "sql_store",
    "user": "root",
    "password": "Jzd@yc107"
}

app = Flask(__name__)

# delete is used for delete
@app.route("/products", methods=["delete"])
def deleteProducts():
    if not request.form:
        abort(400)

    product_id = request.form["product_id"]

    conn = mysql.connector.connect(**config)
    curs = conn.cursor()

    curs.execute(f"""
    delete from products where product_id = {product_id}
    """)

    rowcount = curs.rowcount

    if rowcount > 0:
        conn.commit()

    curs.close()
    conn.close()

    jsonrst = []
    jsonrst.append({
        "rowcount": rowcount
    })
    
    return jsonify(jsonrst)

# put is used for update
@app.route("/products", methods=["put"])
def putProducts():
    if not request.form:
        abort(400)

    product_id = request.form["product_id"]
    name = request.form["name"]
    quantity_in_stock = request.form["quantity_in_stock"]
    unit_price = request.form["unit_price"]
    
    conn = mysql.connector.connect(**config)
    curs = conn.cursor()

    curs.execute(f"""
    update products set
        name = '{name}',
        quantity_in_stock = {quantity_in_stock},
        unit_price = {unit_price}
    where product_id = {product_id}
    """)

    rowcount = curs.rowcount

    if rowcount > 0:
        conn.commit()

    curs.close()
    conn.close()

    jsonrst = []
    jsonrst.append({
        "rowcount": rowcount
    })

    return jsonify(jsonrst)

# post is used for insert
@app.route("/products", methods=["post"])
def postProducts():
    if not request.form:
        abort(400)

    name = request.form["name"]
    quantity_in_stock = request.form["quantity_in_stock"]
    unit_price = request.form["unit_price"]

    conn = mysql.connector.connect(**config)
    curs = conn.cursor()

    curs.execute(f"""
    insert into products (
        name,
        quantity_in_stock,
        unit_price
    )
    values (
        '{name}',
        {quantity_in_stock},
        {unit_price}
    )
    """)

    rowcount = curs.rowcount

    if rowcount > 0:
        conn.commit()

    curs.close()
    conn.close()

    jsonrst = []
    jsonrst.append({
        "rowcount": int(rowcount)
    })

    return jsonify(jsonrst)

# get is used for query/select
@app.route("/products/<int:id>", methods=["get"])
def getProductsById(id):
    conn = mysql.connector.connect(**config)
    curs = conn.cursor()

    curs.execute(f"""
    select
        product_id,
        name,
        quantity_in_stock,
        unit_price
    from
        products
    where
        product_id = {id}
    """)

    rst = curs.fetchall()
    jsonrst = []

    for r in rst:
        jsonrst.append({
            "product_id": int(r[0]),
            "name": str(r[1]),
            "quantity_in_stock": int(r[2]),
            "unit_price": float(r[3])
        })

    curs.close()
    conn.close()

    return jsonify(jsonrst)

# get is used for query/select
@app.route("/products", methods=["get"])
def products():
    conn = mysql.connector.connect(**config)
    curs = conn.cursor()

    curs.execute("""
    select
        product_id,
        name,
        quantity_in_stock,
        unit_price
    from
        products
    """)

    rst = curs.fetchall()
    jsonrst = []

    for r in rst:
        jsonrst.append({
            "product_id": int(r[0]),
            "name": str(r[1]),
            "quantity_in_stock": int(r[2]),
            "unit_price": float(r[3])
        })

    curs.close()
    conn.close()

    return jsonify(jsonrst)

@app.route("/hello/<string:name>", methods=["get"])
def hello(name):
    return f"Hello, {name}!"

if __name__ == "__main__":
    app.run(port=5000, debug=True)